DbcDao2000

Automatic Disconnection

This dll maintains the links to the underlying database and has the ability to disconnect silently from the database when not being used. This is useful where systems are left running but idle, such as leaving machines running overnight. With the automatic disconnect feature, this dll detects that it has not processed any requests recently and disconnects from the database. It automatically re-attaches when systems start using the database again. All this is achieved without the layers above being aware of the disconnect.

• Network databases such as ODBC links can appear more resilent, as brief network breaks while the database is disconnected do not affect the system.
• Shared databases, such as Microsoft Access can run faster if less shared clients are actively connected.
• Overnight maintainance jobs that require exclusive database access do not need to manually close all client programs, which can be tricky in a network.
• This disconnect works for any program using dbcdao2000.dll, be it interactive programs (PosGreen, RetailMAX) or Windows services (PosService), GDS does not typically use this dll.

To enable this feature for Fieldpine POS, add the following line to fpos.ctl (note, it must be in fpos.ctl, not any variation of this file)

dbcdao:disconnect=seconds

"seconds" is the number of seconds with no database requests required before the system will disconnect. For example disconnect=1800 will wait for a period of 30 minutes before disconnecting. Using too small a value may cause problems, as the time to re-attach can be measured in seconds sometimes.

Automatic Recovery

Version 1.0-29.37 introduced a feature to allow operators to repair most types of corrupt databases themselves. Corrupt databases should be rare, but this option allows faster problem repair at counters.

When a corrupt database is detected the logic is:

1. Close any connection to the database
2. Attempt to repair the database, creating the file green_AutoRepairing.mdb
3. If the repair operation worked:
   • Copy the corrupt file to green_AutoRepairBackup.mdb
   • rename green_AutoRepairing.mdb to green.mdb
   • Send an automatic recovery event to advise support staff
   • Log a maintainance work task to check the database
   • Continue with system startup
4. Attempt to JetComp to recovery the database. This is not visible to the user. If this works, follow the same steps above for successful repair.
5. If the database is unrepairable, a future version may start to use the automatic backups.

Automatic Backup

Version 1.0-29.37 added automatic backups to the database. The intention of these backups is to provide some sort of chance to handle catastrophic failure. This is not a replacement for normal backup systems

1. If the setting dbcdao:AutoBackup=0 is not present in fpos.ctl, the automatic backups are enabled
2. A background thread is started to perform the backups. This thread waits 70 seconds to allow POS startup to complete.
3. If the database is ODBC, automatic backups are disabled.
4. A folder "AutoBackup" is created as a subfolder where the database resides.
5. If the "overnight" flag is not set, the system will create 4 files in this folder:
   • green.mdb_Latest. The most recent copy
   • green.mdb_Minus1. The second most recent copy
   • green.mdb_Minus2. The third most recent copy
   • green.mdb_Yesterday. The fourth most recent copy, except that it always lags by one day.
6. However, if the "overnight" flag is set, the system will create a single file in this folder:
   • green.mdb_Overnight.
7. If the database is on a disk that is not the current user default disk drive, a second backup folder is created as a subfolder under the POS folder. The same 4 files are created in this folder. This copy is ensuring a copy is held on an alternative disk.

The parameter AutoBackup is a bitmask. Bit 1 is set to have the autobackup under the folder containing the database. Bit 2 is set to have the autobackup under the current working folder. The default value is "3" to enable both backups. Bit 16 is used to indicate overnight mode.

From version P59, you can specify the AutoBackup flags as keywords rather than numbers

• iflocal (Bit 1) perform backups under the folder containing the database
• ifremote (Bit 2) perform backups under the current working folder
• overnight (Bit 16) select overnight mode, which creates a backup at some time between 2am and 5am.
• disable Disables all AutoBackup functionality

AutoBackup Examples

dbcdao:AutoBackup=iflocal overnight

Copies the database once each night into a subdirectory of the database folder. This method does not create a copy on a second machine so you are still vunerable if the computer fails

dbcdao:AutoBackup=iflocal ifremote

Copies the database reasonably often to a subdirectory of the database folder and also to a subdirectory of the current working folder

dbcdao:AutoBackup=iflocal ifremote overnight

Copies the database once each night to a subdirectory of the database folder and also to a subdirectory of the current working folder

Automatic Compress

Version 1.0-29.58 added automatic compression of Access databases. This enables the database layer to automatically compress the Access database when it determines it is needed. The Point of Sale continues to monitor and report large databases as well, but with this feature enabled the POS messages should be very infrequent.

Automatic compression is enabled with the following line in fpos.ctl or fpos_all.ctl

dbcdao:autocompress=N

where N has the following meaning:

• 0. Disable Auto compression
• 1. Enable Auto compression for overnight only. Setting to 1 also requires that the POS has been running constantly for at least 23 hours. A value of 1 is not generally recommended.
• 3. Enable Auto compression, and permit during the day compressions.
• 5. Enable Auto compression, including during the day, but only if the database is large
• If the parameter autocompress is not defined, the database layer will use the internal defaults.

A normal retailer who typically powers off the counters overnight would use a value of 3. This means the system will attempt to compress the database every few days, and can compress during the day if the system has been idle for several minutes.

A retailer who has enterprise tools and checking might wish to set this parameter to 5. This means the system will compress during the day, but only if the database gets large, which would indicate failure or problems in the external scripts

If the system detects the POS is running for longer than 24 hours, it will automatically schedule all compressions overnight. If a compression is performed during the day, there may be a brief pause/hang if you attempt to use the POS while the compression is in progress. A typical compression takes 30 seconds and only happens every few days, and after the POS has been idle for several minutes, so overall the probability of a brief hang is low

AutoCompress has no effect if the underlying database is not Access

API Functions

With version 1.0-27.1 this DLL offers a number of API functions that can be invoked directly. These allow you to connect to databases and interact with data in bulk. Each of the APIs are designed to be called from rundll32 or similar program.

DumpAPI

The Dump API allows you to extract a single table or all tables from the database.

Example:

rundll32 dbcdao2000.dll,DumpAPI database.mdb,products,products.dump

or

rundll32 dbcdao2000.dll,DumpAPI database.mdb,*,all.dump

or

rundll32 dbcdao2000.dll,DumpAPI "ODBC;dsn=sqlbox;database=finance",*,all.dump

---

DebugAPI

The Debug API dumps static information about the database. This option was first available in P29 of the DLL.

Examples:

Display static information about the database, specifically internal database format

rundll32 dbcdao2000.dll,DebugAPI info New.mdb

Change a field definition to AllowZeroLength. Requires version P45 or higher

rundll32 dbcdao2000.dll,DebugAPI 121010 theDatabase.mdb,f602(83) f603(TableName) f607(FieldName)

---

BuildAPI

The Build API uses a dump file to create a copy of the original database or table. Copies are designed for support and testing purposes rather than being instantly production ready. For example, indexes in the original source database may not be preserved

Example:

rundll32 dbcdao2000.dll,BuildAPI New.mdb,all.dump

LoadAPI

The Load API allows you to insert rows into table or tables from a file created by the Dump API. Typically used in a distributed system to bulk load and replicate data to remote databases.

Command	Description
db(N)	Specifies the destination database
file(N)	Sets the input file containing the source records to be loaded
table(N)	Sets the output table. This parameter is optional and if not given all tables in the input file will be loaded to the original table names they were extracted from.
key(N)	Defines the primary key for the table. If not provided the system will attempt to determine this information from the database indexes present. Key information is not required if mode(reset) is used
delete(N)	Control whether the input file is deleted after processing is completed. Use '1' to delete the file or '0' to keep the file. The default is to delete the input file
mode(N)	Sets how records are to be merged into the target table. reset The target table is emptied before loading. This is typically very fast at loading records but may have a small timing window where valid records are not present add Records are added and updated as needed, but no records are deleted

Example:

rundll32 dbcdao2000.dll,LoadAPI db(New.mdb) file(products.dump) table(products)

CloneAPI

Not currently documented for external use.